﻿namespace Guru.Framework.DBConnection
{
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.Common;
    using System.Data.OleDb;
    using System.Data.SqlClient;

    internal class Connection : IConnection
    {
        #region [ Private Fields ]

        private DbConnection dbConn;
        private DbTransaction dbTrans;

        private string _ConnString = string.Empty;
        private ConnectionTypes _ConnType = ConnectionTypes.SqlServer;

        #endregion

        #region [ Connection Constructor with Connection Type And Connection String ]

        /// <summary>
        /// Creates Connection Object with given ConnectionType And ConnectionString.
        /// </summary>
        /// <param name="ConnectionType">Connection Type</param>
        /// <param name="connectionString">Connection String</param>
        public Connection(ConnectionTypes ConnectionType, String connectionString)
        {
            _ConnString = connectionString;
            _ConnType = ConnectionType;
            dbConn = CreateConnection();
            dbConn.ConnectionString = connectionString;
        }

        #endregion


        #region [ ConnectionType Property Of Connection ]

        /// <summary>
        ///  ConnectionType Of Connection.
        /// </summary>
        public ConnectionTypes ConnectionType
        {
            get
            {
                return _ConnType;
            }
            set
            {
                DisposeConnection();
                _ConnType = value;
                dbConn = CreateConnection();
                dbConn.ConnectionString = _ConnString;
            }
        }

        #endregion


        #region [ ConnectionString Property Of Connection ]

        /// <summary>
        /// Connection String Of Connection.
        /// </summary>
        public string ConnectionString
        {
            get
            {
                return _ConnString;
            }
            set
            {
                _ConnString = value;
                dbConn.ConnectionString = _ConnString;
            }
        }

        #endregion


        #region [ Dispose Method ]

        /// <summary>
        /// Disposes Connection object.
        /// </summary>
        public void Dispose()
        {
            try
            {
                DisposeConnection();
                GC.SuppressFinalize(this);
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion


        #region [ Create Connection with Connection Type]
        /// <summary>
        /// Returns a IDbConnection object instance.
        /// </summary>
        /// <param name="conType">Connection Type</param>
        /// <returns>Returns a IDbConnection object instance.</returns>
        protected DbConnection CreateConnection()
        {
            try
            {
                switch (_ConnType)
                {
                    case ConnectionTypes.SqlExpress:
                    case ConnectionTypes.SqlServer:
                        return new SqlConnection();

                    case ConnectionTypes.PostgreSQL:
                        throw new NotSupportedException("PostgreSQL Driver is not supported.");

                    case ConnectionTypes.DB2:
                        throw new NotSupportedException("DB2 Driver is not supported.");

                    case ConnectionTypes.OleDb:
                        return new OleDbConnection();

                    case ConnectionTypes.FireBird:
                        throw new NotSupportedException("FireBirdSQL Driver is not supported.");

                    case ConnectionTypes.Oracle:
                        //   return new OracleConnection();
                        throw new NotSupportedException("Oracle Driver is not supported.");

                    case ConnectionTypes.SQLite:
                        throw new NotSupportedException("SQLite Driver is not supported.");

                    case ConnectionTypes.MariaDB:
                    case ConnectionTypes.MySQL:
                        throw new NotSupportedException("MySQL Driver is not supported.");

                    case ConnectionTypes.VistaDB:
                        throw new NotSupportedException("VistaDB Driver is not supported.");

                    case ConnectionTypes.SqlServerCe:
                        throw new NotSupportedException("SqlServerCe Driver is not supported.");

                    default:
                        throw new NotSupportedException("UnSupported Driver Type");
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion


        #region [ Open Connection ]

        public void OpenConnection()
        {
            try
            {
                dbConn.Open();
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion


        #region [ Close Connection ]

        public void CloseConnection()
        {
            try
            {
                if (null != dbConn)
                {
                    if (GetConnectionState() == ConnectionState.Open)
                        dbConn.Close();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion


        #region [ Dispose Connection Method ]

        protected void DisposeConnection()
        {
            try
            {
                CloseConnection();
                dbConn.Dispose();
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion


        #region [ Get Connection State ]

        public ConnectionState GetConnectionState()
        {
            return dbConn.State;
        }

        #endregion


        #region [ Create Transaction for DbConnection ]

        public void CreateTransaction()
        {
            try
            {
                if (dbConn.State != ConnectionState.Open)
                {
                    dbConn.Open();
                }

                dbTrans = dbConn.BeginTransaction();
            }
            catch (Exception)
            { throw; }
        }

        #endregion


        #region [ Commit And Dispose Transaction ]

        public void CommitTransaction()
        {
            try
            {
                if (dbTrans != null)
                {
                    dbTrans.Commit();
                    dbTrans.Dispose();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion


        #region [ Rollback And Dispose Transaction ]

        public void RollbackTransaction()
        {
            try
            {
                if (dbTrans != null)
                {
                    dbTrans.Rollback();
                    dbTrans.Dispose();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion


        #region [ Create Data Adapter with Connection Type ]

        /// <summary>
        /// Returns a IDataAdapter object instance.
        /// </summary>
        /// <param name="conType">Connection Type</param>
        /// <returns>Returns a IDataAdapter object instance.</returns>
        protected DbDataAdapter CreateAdapter(DbCommand dbCmd)
        {
            try
            {
                switch (_ConnType)
                {
                    case ConnectionTypes.SqlExpress:
                    case ConnectionTypes.SqlServer:
                        return new SqlDataAdapter((SqlCommand)dbCmd);

                    case ConnectionTypes.PostgreSQL:
                        throw new NotSupportedException("Npgsql Driver is not supported.");

                    case ConnectionTypes.DB2:
                        throw new NotSupportedException("DB2 Driver is not supported.");

                    case ConnectionTypes.Oracle:
                        //return new OracleDataAdapter((OracleCommand)dbCmd);
                        throw new NotSupportedException("Oracle Driver is not supported.");

                    case ConnectionTypes.MariaDB:
                    case ConnectionTypes.MySQL:
                        throw new NotSupportedException("MySQL Driver is not supported.");

                    case ConnectionTypes.OleDb:
                        return new OleDbDataAdapter((OleDbCommand)dbCmd);

                    case ConnectionTypes.SQLite:
                        throw new NotSupportedException("SQLite Driver is not supported.");

                    case ConnectionTypes.FireBird:
                        throw new NotSupportedException("FireBirdSQL Driver is not supported.");

                    case ConnectionTypes.VistaDB:
                        throw new NotSupportedException("VistaDB Driver is not supported.");

                    case ConnectionTypes.SqlServerCe:
                        throw new NotSupportedException("SqlServerCe Driver is not supported.");

                    default:
                        throw new NotSupportedException("UnSupported Driver Type");
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion


        #region [ Get Result Set ]

        public DataSet GetResultSet(string query)
        {
            try
            {
                DataSet ds = new DataSet();

                using (DbCommand dbCmd = dbConn.CreateCommand())
                {
                    if (dbTrans != null)
                        dbCmd.Transaction = dbTrans;

                    dbCmd.CommandText = query;
                    using (DataAdapter dbAdapter = CreateAdapter(dbCmd))
                    {
                        dbAdapter.Fill(ds);
                    }
                }

                return ds;
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion


        #region [ Get Result Set With Parameters ]

        public DataSet GetResultSet(string query, Hashtable parameters)
        {
            try
            {
                DataSet ds = new DataSet();
                using (DbCommand dbCmd = dbConn.CreateCommand())
                {

                    if (dbTrans != null)
                        dbCmd.Transaction = dbTrans;

                    dbCmd.CommandText = query;
                    if (null != parameters)
                    {
                        IDictionaryEnumerator prmtr = parameters.GetEnumerator();
                        DbParameter dbParam;
                        while (prmtr.MoveNext())
                        {
                            dbParam = dbCmd.CreateParameter();

                            dbParam.ParameterName = prmtr.Key.ToString();
                            dbParam.Value = prmtr.Value;
                            dbCmd.Parameters.Add(dbParam);
                        }
                    }
                    using (DataAdapter dbAdapter = CreateAdapter(dbCmd))
                    {
                        dbAdapter.Fill(ds);
                    }
                }
                return ds;
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion


        #region [ Execute Query ]

        public int ExecuteQuery(string query)
        {
            try
            {
                int retInt;
                using (DbCommand dbCmd = dbConn.CreateCommand())
                {

                    if (dbTrans != null)
                        dbCmd.Transaction = dbTrans;

                    dbCmd.CommandText = query;
                    retInt = dbCmd.ExecuteNonQuery();
                }
                return retInt;
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion


        #region [ Execute Query With Parameters ]

        public int ExecuteQuery(string query, Hashtable parameters)
        {
            try
            {
                int retInt;
                using (DbCommand dbCmd = dbConn.CreateCommand())
                {

                    if (dbTrans != null)
                        dbCmd.Transaction = dbTrans;

                    dbCmd.CommandText = query;
                    if (null != parameters)
                    {
                        IDictionaryEnumerator prmtr = parameters.GetEnumerator();
                        DbParameter dbParam;
                        while (prmtr.MoveNext())
                        {
                            dbParam = dbCmd.CreateParameter();

                            dbParam.ParameterName = prmtr.Key.ToString();
                            dbParam.Value = prmtr.Value;
                            dbCmd.Parameters.Add(dbParam);
                        }
                    }
                    retInt = dbCmd.ExecuteNonQuery();
                    dbCmd.Parameters.Clear();
                }
                return retInt;
            }
            catch (Exception)
            {
                throw;
            }

        }

        #endregion


        #region [ Execute Scalar ]

        public object ExecuteScalar(string query)
        {
            try
            {
                object retObj;
                using (DbCommand dbCmd = dbConn.CreateCommand())
                {

                    if (dbTrans != null)
                        dbCmd.Transaction = dbTrans;

                    dbCmd.CommandText = query;
                    retObj = dbCmd.ExecuteScalar();
                }
                return retObj;
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion


        #region [ Execute Scalar With Parameters ]

        public object ExecuteScalar(string query, Hashtable parameters)
        {
            try
            {
                object retObj;
                using (DbCommand dbCmd = dbConn.CreateCommand())
                {

                    if (dbTrans != null)
                        dbCmd.Transaction = dbTrans;

                    dbCmd.CommandText = query;
                    if (null != parameters)
                    {
                        IDictionaryEnumerator prmtr = parameters.GetEnumerator();
                        DbParameter dbParam;
                        while (prmtr.MoveNext())
                        {
                            dbParam = dbCmd.CreateParameter();

                            dbParam.ParameterName = prmtr.Key.ToString();
                            dbParam.Value = prmtr.Value;
                            dbCmd.Parameters.Add(dbParam);
                        }
                    }
                    retObj = dbCmd.ExecuteScalar();
                    dbCmd.Parameters.Clear();
                }
                return retObj;
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion

    }
}
